﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc.Fc
{
    public partial class 接箍生产日进度报表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected int[] Data = new int[66];

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                RiQi.Text = DateTime.Today.AddDays(-1).ToString("yyyy年M月d日");
            }
            this.QueryData();
        }

        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("三分厂日进度[" + d.ToLongDateString()) + "].xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("报表/模板/三分厂生产进度日报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "接箍日生产报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {

            InitializeWorkbook();
            this.QueryData();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("数据");
            int r = 0;
            HSSFRow row;
            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("日期");
            row.CreateCell(1).SetCellValue(this.RiQi.Text);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("生产单位");
            row.CreateCell(1).SetCellValue("三分厂");

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("负责人");
            row.CreateCell(1).SetCellValue("");

            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "";
            }

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("制表人");
            row.CreateCell(1).SetCellValue(op);

            
            for (int i = 1; i <= 20; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue("车丝"+i);
                row.CreateCell(1).SetCellValue(Data[i-1]);
            }

            for (int i = 1; i <= 20; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue("车丝" + i+"累计");
                row.CreateCell(1).SetCellValue(Data[i+20 - 1]);
            }

            for (int i = 1; i <= 20; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue("车丝" + i + "年累计");
                row.CreateCell(1).SetCellValue(Data[i + 40 - 1]);
            }

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("套管接箍入库");
            row.CreateCell(1).SetCellValue(Data[63]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("套管接箍入库累计");
            row.CreateCell(1).SetCellValue(Data[64]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("套管接箍入库年累计");
            row.CreateCell(1).SetCellValue(Data[65]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("油管接箍入库");
            row.CreateCell(1).SetCellValue(Data[60]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("油管接箍入库累计");
            row.CreateCell(1).SetCellValue(Data[61]);

            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("油管接箍入库年累计");
            row.CreateCell(1).SetCellValue(Data[62]);

            sheet1.ForceFormulaRecalculation = true;
            HSSFSheet sheet2 = hssfworkbook.GetSheet("报表");
            sheet2.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

        private void QueryData()
        {
            DateTime d1, d2, d3;
            if (!DateTime.TryParse(RiQi.Text, out d1))
            {
                return;
            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;

            Cmd.CommandText =
@"declare @y int
declare @d1 smalldatetime
if exists(select * from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期)
begin
	select @y=年度,@d1=起始日期 from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期
	if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1)
	begin
		select @d1 as 月累计日期,起始日期 as 年累计日期 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1
	end
end";
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "3");
            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
                this.RiQi2.Text = d2.ToLongDateString();
                this.RiQi3.Text = d3.ToLongDateString();
            }
            else
            {
                this.RiQi2.Text = d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日";
                this.RiQi3.Text = d1.Year.ToString() + "年1月1日";
                d2 = DateTime.Parse(this.RiQi2.Text);
                d3 = DateTime.Parse(this.RiQi3.Text);
            }
            Reader.Close();


            Cmd.CommandText = "三分厂日进度数据";
            Cmd.CommandType = CommandType.StoredProcedure;

            Cmd.Parameters.Add(new SqlParameter("@d1", SqlDbType.SmallDateTime, 4, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, d1));
            Cmd.Parameters.Add(new SqlParameter("@d2", SqlDbType.SmallDateTime, 4, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, d2));
            Cmd.Parameters.Add(new SqlParameter("@d3", SqlDbType.SmallDateTime, 4, ParameterDirection.Input, true, 0, 0, "", DataRowVersion.Proposed, d3));

            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                for (int i = 0; i < 66; i++)
                {
                    Data[i] = (int)Reader[i];
                }
            }
            Reader.Close();
            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }
    }
}
